#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class CountryDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new Country instance filled with the DataReader's current record data 
	     /// </summary>
	     private Country GetCountryFromReader(IDataReader dr)
	     {
		     Country obj = new Country();
		     obj.CountryID = (dr["CountryID"] is DBNull) ? Int32.MinValue :  (Int32)dr["CountryID"];
		     obj.CountryCode = (dr["CountryCode"] is DBNull) ? String.Empty :  (String)dr["CountryCode"];
		     obj.CountryName = (dr["CountryName"] is DBNull) ? String.Empty :  (String)dr["CountryName"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of Country with the data read from the input DataReader
	     /// </summary>
	     private List<Country> GetCountryCollectionFromReader(IDataReader dr)
	     {
		     List<Country> obj = new List<Country>();
		     while(dr.Read())
			     obj.Add(GetCountryFromReader(dr));
		     return obj;
	     }


         public int Insert(Country obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("Job_Country_Insert", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = obj.CountryID;
                 myCommand.Parameters.Add("@CountryCode", SqlDbType.Char).Value = obj.CountryCode;
                 myCommand.Parameters.Add("@CountryName", SqlDbType.NVarChar).Value = obj.CountryName;

                 return (int)myCommand.ExecuteNonQuery();
             }
         }



         /// <summary>
         /// Retrieves the Job_Country with the specified ID
         /// </summary>
         public Country GetCountryByID(Int32 CountryID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("Job_Country_GetCountryByID", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = CountryID;

                 SqlDataReader dr = myCommand.ExecuteReader();
                 return (dr.Read() ? GetCountryFromReader(dr) : null);
             }
         }


	     //Get All Country
	     public List<Country> GetAllCountry()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Country_GetAllCountry", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetCountryCollectionFromReader(dr);
		     }
	     }

	 }
}
